﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using ComExcel = Microsoft.Office.Interop.Excel;
using AF_ORM.CORE;
using System.Configuration;
namespace DSC.uc
{
    public partial class ucExportData : AfUserControl
    {
        Hashtable hshTable = new Hashtable();
        string BackupDirectory = "D:\\BackupDB";
        string DesDirectory = "D:\\BackupDB";      
        string BackupMDF = "PTS_RE1";
        string BackupLDF = "PTS_RE1_Log";
        string DBName = "PTS_RE1";


        private void LoadConfig()
        {
            BackupDirectory = ConfigurationManager.AppSettings["BackupDirectory"];
            DesDirectory = ConfigurationManager.AppSettings["DesDirectory"];            
            BackupMDF = ConfigurationManager.AppSettings["BackupMDF"];
            BackupLDF = ConfigurationManager.AppSettings["BackupLDF"];
            DBName = ConfigurationManager.AppSettings["DATABASE"];
        }

        public ucExportData()
        {
            hshTable.Add("PT_ServiceBill", "Bill_NO,ServiceType,InputDate,OutputDate,RepairDate,FinishDate,RepairedBy,RepairedBy2,ReceivedBy,Cashier,CheckBy,CustName,CustName_noUnicode,CellPhone,Phone,AddressNumber,Address,ProvinceCode,DistCode,Ward,Model,TotalKms,NumberPlate,MachineCode,Comment,ReportDate,IsUseOil,IsFinished,IsDeleted,CreatedBy,CreatedDate,UpdatedBy,UpdatedDate");
            hshTable.Add("PT_ServiceBillDetail", "Id,Bill_NO,ServiceId,Amount,OutPaid,Comment,SaleOffMoney,SaleOffReason,Price,AgentPrice,IsDeleted,CreatedBy,CreatedDate,UpdatedBy,UpdatedDate,ServiceCode,IntCode");
            hshTable.Add("PT_ToolInBill", "Id,BillNo,Part_No,Part_Name,QTY,OriginalPrice,Price_After_Discount,Amount,ServiceType,ToolType,IsOil,DiscountReason");
            hshTable.Add("PT_Customer", "NumberPlate,CustName,CellPhone,Phone,AddressNumber,Address,ProvinceCode,DistCode,Ward,Model,TotalKms,MachineCode");
            hshTable.Add("PT_Account", "UserID,UserCode,UserName,DisplayName,DOB,Address,Password,RoleId,DEALER_CODE,IsSystem,IsActive,IsDeleted,CreatedBy,CreatedDate,UpdatedBy,UpdatedDate");
            hshTable.Add("PT_ServiceList", "Id,ServiceCode,ServiceName,CanEdit,CanEditNoiDung,CanEditGCN,CanEditGiamGia,CanEditGiaBan,ServiceType,IsDeleted,CreatedBy,CreatedDate,UpdatedBy,UpdatedDate");
            hshTable.Add("PT_Service_Model", "Id,ServiceCode,ModelCode,AgentPrice,Price");
            hshTable.Add("PT_HondaModel", "ModelCode,ModelName,OrderIndex,GroupCode,IsActive,IsDeleted,CreatedBy,CreatedDate,UpdatedBy,UpdatedDate");
            hshTable.Add("PT_HondaModelGroup", "GroupCode,GroupName,OrderIndex,IsActive,IsDeleted,CreatedBy,CreatedDate,UpdatedBy,UpdatedDate");
          //  hshTable.Add("PT_ActionHistory", "Id,UserId,ActionDate,ActionName,Description");
            
            InitializeComponent();
            LoadConfig();
            LoadBackupList();
        }

        public void ExportToSheet(string tableName, ComExcel.Worksheet sheet)
        {
            string[] columns = hshTable[tableName].ToString().Split(',');
            for (int i = 0; i < columns.Length; i++)
            {
                sheet.Cells[1, i+1] = columns[i];
            }
            //lay du lieu tu DB
            string sql = " select * from " + tableName + " where isdeleted = 0 order by createddate";
            DataTable tbl = SqlHelper.ExecuteDataTable(Bussiness.Config.ConnectionConfig.ConnectionString, CommandType.Text, sql);
            for(int i = 0;i<tbl.Rows.Count;i++)
            {
                for (int c = 0; c < columns.Length; c++)
                    sheet.Cells[i + 2, c + 1] = tbl.Rows[i][columns[c]].ToString();
            }
        }
        public void Export()
        {
            SaveFileDialog saveFileExcel = new SaveFileDialog();
            saveFileExcel.FileName = "PTS_Data_" + DateTime.Now.ToString("dd_MM_yyyy_HH_mm") + ".xls";
            saveFileExcel.DefaultExt = "xls";
            saveFileExcel.Filter = "Excel 2003 Files(*.xls)|*.xls|Excel 2007(*.xlsx)|*.xlsx|All Files|*.*";
            saveFileExcel.AddExtension = true;
            saveFileExcel.RestoreDirectory = true;
            saveFileExcel.Title = "Chọn đường dẫn lưu file";
            //saveFileExcel.InitialDirectory = @"C:/";
            if (saveFileExcel.ShowDialog() == DialogResult.OK)
            {
                if (saveFileExcel.FileName.Split('\\')[saveFileExcel.FileName.Split('\\').Length - 1] == ".xls")
                {
                    MessageBox.Show("Nhập tên file cần lưu");
                }
                else
                {
                    // Khoi dong Excel
                    ComExcel.Application exApp = new ComExcel.Application();

                    //// them file temp xls moi
                    //ComExcel.Workbook exBook = exApp.Workbooks.Add(ComExcel.XlWBATemplate.xlWBATWorksheet);

                    // Mo mot file Excel co san
                    ComExcel.Workbook exBook = exApp.Workbooks.Add(ComExcel.XlWBATemplate.xlWBATWorksheet);
                    //ComExcel.Workbook exBook = exApp.Workbooks.Add(Application.StartupPath + "/ExcelTemplate/PTS_Data.xls");


                    //tao sheet trong excel
                    foreach (DictionaryEntry entry in hshTable)
                    {
                        ComExcel.Worksheet sheet =(ComExcel.Worksheet) exBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                        sheet.Name = entry.Key.ToString();
                        ExportToSheet(entry.Key.ToString(), sheet);
                        
                    }
                    // Lay sheet 1
                    //ComExcel.Worksheet exSheet = (ComExcel.Worksheet)exBook.Worksheets[1];
                    
                    //exSheet.Activate();

                    //// dia chi 
                    //DataTable headdt = PT_ServiceBillManager.CreateInstant().GetReportHead(SessionLogin.UserId).Tables[0];
                    //exSheet.Cells[3, 1] = headdt.Rows[0]["HEAD_ADD"];

                    ////tu ngay- den ngay - dong 6 , cot 9
                    //exSheet.Cells[6, 7] = "Từ ngày: " + fromDate2.Value.ToString("dd/MM/yyyy") + " đến ngày: " + toDate2.Value.ToString("dd/MM/yyyy");

                    //////cau hình chung
                    ////ComExcel.Range rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[8, 1], exSheet.Cells[8 + dt.Rows.Count, HondaTypeList.Count]);
                    ////rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                    ////rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                    ////rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                    ////rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                    //ComExcel.Range rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[11, 1], exSheet.Cells[1000, 25]);
                    //rng.Font.Size = 9;
                    //rng.WrapText = true;
                    //// đổ dữ liệu
                    //for (int i = 0; i < dt.Rows.Count; i++)
                    //{
                    //    rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[11 + i, 1], exSheet.Cells[11 + i, 25]);
                    //    rng.Interior.Pattern = ComExcel.Constants.xlSolid;
                    //    rng.Borders.LineStyle = ComExcel.XlLineStyle.xlContinuous;
                    //    rng.VerticalAlignment = ComExcel.XlVAlign.xlVAlignCenter;
                    //    rng.HorizontalAlignment = ComExcel.XlHAlign.xlHAlignCenter;
                    //    exSheet.Cells[11 + i, 1] = dt.Rows[i]["STT"].ToString();
                    //    exSheet.Cells[11 + i, 2] = dt.Rows[i]["SoPhieu"].ToString();
                    //    exSheet.Cells[11 + i, 3] = dt.Rows[i]["NgayDenSuaChua"].ToString();
                    //    exSheet.Cells[11 + i, 4] = dt.Rows[i]["LoaiXe"].ToString();
                    //    exSheet.Cells[11 + i, 5] = dt.Rows[i]["SoKM"].ToString();
                    //    exSheet.Cells[11 + i, 6] = "'" + dt.Rows[i]["SoKhung"].ToString();
                    //    exSheet.Cells[11 + i, 7] = "'" + dt.Rows[i]["BienSo"].ToString();

                    //    if (i != dt.Rows.Count - 1)
                    //    {
                    //        int luotrap = int.Parse(dt.Rows[i]["SLRapBan"].ToString());
                    //        int luotktdk = int.Parse(dt.Rows[i]["SLKTDK"].ToString());
                    //        int luotsuachua = int.Parse(dt.Rows[i]["SLSuaChua"].ToString());
                    //        if (luotrap >= 1)
                    //        {
                    //            exSheet.Cells[11 + i, 8] = "'1";
                    //            exSheet.Cells[11 + i, 9] = "'0";
                    //            exSheet.Cells[11 + i, 10] = "'0";
                    //        }
                    //        else if (luotktdk >= 1)
                    //        {
                    //            exSheet.Cells[11 + i, 8] = "'0";
                    //            exSheet.Cells[11 + i, 9] = "'1";
                    //            exSheet.Cells[11 + i, 10] = "'0";
                    //        }
                    //        else if (luotsuachua >= 1)
                    //        {
                    //            exSheet.Cells[11 + i, 8] = "'0";
                    //            exSheet.Cells[11 + i, 9] = "'0";
                    //            exSheet.Cells[11 + i, 10] = "'1";
                    //        }
                    //    }
                    //    else
                    //    {
                    //        exSheet.Cells[11 + i, 8] = "'" + dt.Rows[i]["SLRapBan"].ToString();
                    //        exSheet.Cells[11 + i, 9] = "'" + dt.Rows[i]["SLKTDK"].ToString();
                    //        exSheet.Cells[11 + i, 10] = "'" + dt.Rows[i]["SLSuaChua"].ToString();
                    //    }
                    //    exSheet.Cells[11 + i, 11] = dt.Rows[i]["TienCong"].ToString();
                    //    exSheet.Cells[11 + i, 12] = dt.Rows[i]["TienPTThayThe"].ToString();
                    //    exSheet.Cells[11 + i, 13] = dt.Rows[i]["TienNhot"].ToString();
                    //    exSheet.Cells[11 + i, 14] = dt.Rows[i]["TongCong"].ToString();
                    //    exSheet.Cells[11 + i, 15] = dt.Rows[i]["HoTenKH"].ToString();
                    //    exSheet.Cells[11 + i, 16] = dt.Rows[i]["SoNha"].ToString();
                    //    exSheet.Cells[11 + i, 17] = dt.Rows[i]["TenDuong"].ToString();
                    //    exSheet.Cells[11 + i, 18] = dt.Rows[i]["Phuong"].ToString();
                    //    exSheet.Cells[11 + i, 19] = dt.Rows[i]["Quan"].ToString();
                    //    exSheet.Cells[11 + i, 20] = dt.Rows[i]["Tp"].ToString();
                    //    exSheet.Cells[11 + i, 21] = "'" + dt.Rows[i]["DienThoai"].ToString();
                    //    exSheet.Cells[11 + i, 22] = dt.Rows[i]["NVTiepNhan"].ToString();
                    //    exSheet.Cells[11 + i, 23] = "'" + dt.Rows[i]["MaNVTiepNhan"].ToString();
                    //    exSheet.Cells[11 + i, 24] = dt.Rows[i]["KTV"].ToString();
                    //    exSheet.Cells[11 + i, 25] = "'" + dt.Rows[i]["MaKTV"].ToString();
                    //}
                    //rng = (ComExcel.Range)exSheet.get_Range(exSheet.Cells[10 + dt.Rows.Count, 1], exSheet.Cells[10 + dt.Rows.Count, 25]);
                    //rng.Font.Bold = true;
                    //// hien thi chuong trinh excel

                    exBook.SaveAs(saveFileExcel.FileName, ComExcel.XlFileFormat.xlWorkbookNormal, null, null, false, false, ComExcel.XlSaveAsAccessMode.xlExclusive, false, false, false, false, false);
                    DialogResult dlg = MessageBox.Show("File báo cáo được lưu ở đường dẫn sau: " + saveFileExcel.FileName + Environment.NewLine + "Bạn có muốn mở file không ?", "Thông Báo", MessageBoxButtons.YesNo);
                    if (dlg == DialogResult.Yes)
                    {
                        // hien thi chuong trinh excel
                        exApp.Visible = true;
                        exBook.ReadOnlyRecommended = false;
                    }
                    else
                    {
                        exBook.Close(false, false, false);
                        exApp.Quit();
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(exBook);
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(exApp);
                    }
                    //MessageBox.Show("File báo cáo được lưu ở đường dẫn sau: " + saveFileExcel.FileName);
                    
                }
            }
        }
        private void LoadBackupList()
        {
            try
            {
                string strSql = @" EXEC sp_configure 'show advanced options', 1
                            
                            --Update the currently configured value for advanced options.
                            RECONFIGURE
                            
                            --Enable XP_CMDSHELL
                            EXEC sp_configure 'xp_cmdshell', 1
                            
                            --Update the currently configured value for this feature.
                            RECONFIGURE";
                string ConnectionString = @"server=" + ConfigurationManager.AppSettings["SERVER"] + ";database=master;uid=" + Encrypt.FDecrypt(ConfigurationManager.AppSettings["UserName"]) + ";pwd=" + Encrypt.FDecrypt(ConfigurationManager.AppSettings["Password"]);
                SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, strSql);

                strSql = @"EXEC MASTER.dbo.xp_cmdshell 'DIR D:\BackupDB /A-D  /B'";
                DataTable tbl = SqlHelper.ExecuteDataTable(ConnectionString, CommandType.Text, strSql);
             
                tbl.DefaultView.RowFilter = "output like '%.bak%'";
                tbl.DefaultView.Sort = "output DESC";
                cboFile.DataSource = tbl.DefaultView;
                cboFile.DisplayMember = "output";
                cboFile.ValueMember = "output";
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

        }
        private void btnExport_Click(object sender, EventArgs e)
        {
            ShowStatusBar("Đang xuất dữ liệu excel");
            Export();
            EndShowStatusBar();
        }

        private void cboFile_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void btnBackup_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(txtDescription.Text))
            {
                MessageBox.Show("Vui lòng nhập ghi chú file sao lưu.");
                txtDescription.Focus();
                return;
            }
            try
            {
                Backup();
                MessageBox.Show("Đã sao lưu thành công CSDL");
                LoadBackupList();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
           
        }

        private void Backup()
        {
            string ConnectionString = @"server=" + ConfigurationManager.AppSettings["SERVER"] + ";database=master;uid=" + Encrypt.FDecrypt(ConfigurationManager.AppSettings["UserName"]) + ";pwd=" + Encrypt.FDecrypt(ConfigurationManager.AppSettings["Password"]);
            string filePath = BackupDirectory + @"\" + DateTime.Now.ToString("yyyyMMddhhmmss")+ "-" + SessionLogin.UserCode + "-" + txtDescription.Text + @".Bak";
            //SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "EXEC master.dbo.SP_MP_DB_BACKUP @Path = '" + ConfigurationManager.AppSettings["Path"] + "', @dbType = 'user', @Name='" + ConfigurationManager.AppSettings["DBBackup"] + "', @Retention = 2");
            string strSql = @"BACKUP DATABASE " + ConfigurationManager.AppSettings["DATABASE"] + @"
                                TO DISK = '" + filePath + @"'
                                        WITH FORMAT,
                                        MEDIANAME = 'Z_SQLServerBackups',
                                        NAME = 'Auto backup " + DateTime.Now.ToString("yyyyMMddhhmmss") + @"' ;";
            SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, strSql);
           
        }

        private void Restore()
        {
            string strSql = @"RESTORE FILELISTONLY FROM  DISK = N'" + BackupDirectory + @"\" + cboFile.SelectedValue + @"'
                      
                        ALTER DATABASE " + DBName + @" SET SINGLE_USER WITH ROLLBACK IMMEDIATE
                      
                        -- RESTORE The database...this will overlay an existing one, OR Create a new one if not in existence
                        -- Get the Logical file names from filelistonly

                        RESTORE DATABASE " + DBName + @"
                        FROM DISK = N'" + BackupDirectory + @"\" + cboFile.SelectedValue + @"'	
                            WITH MOVE '" + BackupMDF + @"'	TO '" + DesDirectory + @"\" + BackupMDF + ".MDF'"
                               + @" ,MOVE '" + BackupLDF + @"'	TO '" + DesDirectory + @"\" + BackupLDF + ".LDF'"
	                        + @", REPLACE
                        ALTER DATABASE PTS_RE1 SET READ_WRITE
                        ALTER DATABASE PTS_RE1 SET MULTI_USER
                       ";
            string ConnectionString = @"server=" + ConfigurationManager.AppSettings["SERVER"] + ";database=master;uid=" + Encrypt.FDecrypt(ConfigurationManager.AppSettings["UserName"]) + ";pwd=" + Encrypt.FDecrypt(ConfigurationManager.AppSettings["Password"]);
            SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, strSql);// @Path = 'D:\BackupDB\', @dbType = 'user', @Name='PTS', @Retention = 2");

        }

        private void btnRestore_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("Dữ liệu hiện tại sẽ bị mất nếu bạn phục hồi. Bạn có tiếp tục phục hồi?", "Cảnh báo", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) == DialogResult.Yes)
            {
                try
                {
                    Restore();
                    MessageBox.Show("Đã phục hồi CSDL thành công");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
            }
        }
    }
}
